#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @FileName  :python_flask_mysql.py
# @Time      :2025/2/14 12:00
# @Author    :kk
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
HOSTNAME = "10.15.82.74"
PORT = 3306
USERNAME = "root"
PASSWORD = "123456"
DATABASE = "flask_demo"
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4".format(
    USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE
)
db = SQLAlchemy(app)
migrate = Migrate(app,db)

# ORM 模型映射成表的三步
# 需要将文件名改为 app.py
# 1. flask db init :这步只需要执行一次
# 2. flask db migrate :识别ORM模型的改变,生成迁移脚本
# 3. flask db upgrade :执行迁移脚本,同步到数据库

# 实体表
class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    password = db.Column(db.String(50), nullable=False)
    age = db.Column(db.Integer, nullable=False)
    sex = db.Column(db.String(10), nullable=False)

# user = User(name="csskd",password="123456",age=18)
# sql: insert into user(name,password,age) values('csskd','123456',18)

class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)

    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # backref 会自动给User模型添加一个articles的属性，用来获取文章列表
    author = db.relationship("User",backref="articles")

# article = Article(title="csskd title",content="csskd content")
#article.author_id = user.id
#user = User.query.get(article.author_id)
#article.author = User.query.get(article.author_id)
#print(article.author)


#with app.app_context():
    # 新增模型无法更新
#    db.create_all()

# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("select 1"))
#         print(rs.fetchone()) #(1,)





@app.route("/user/add")
def user_add():
    # 1.创建ORM对象
    user = User(name="csskd",password="123456",age=18)
    # 2. 将ORM对象添加到数据库会话中
    db.session.add(user)
    # 3. 将数据库会话提交到数据库中
    db.session.commit()
    return "添加成功"

@app.route("/user/query")
def user_query():
    # 1. get查找:根据主键查找
    user = User.query.get(1)
    print(f"主键查询: {user.id}: {user.name} - {user.password}")
    # 2. filter_by 查找:根据条件查找
    # Query： 类数组
    users = User.query.filter_by(age=18)
    for user in users:
        print(f"查找的数据:{user.name}")
    return "数据查找成功"

@app.route("/user/update")
def user_update():
    # 1. 根据主键查找
    user = User.query.filter_by(age=18).first()
    user.password = "00000"
    db.session.commit()
    return "数据修改成功！"

@app.route("/user/delete")
def user_delete():
    # 1. 根据主键查找
    user = User.query.filter_by(age=18).first()
    db.session.delete(user)
    db.session.commit()
    return "数据删除成功！"

@app.route("/article/add")
def article_add():
    article = Article(title="csskd title1",content="csskd content1")
    article.author_id = 2

    article2 = Article(title="csskd title2", content="csskd content2")
    article2.author = User.query.get(2)
    db.session.add_all([article,article2])
    db.session.commit()
    return "文章添加成功！"

@app.route("/article/query")
def article_query():
    user = User.query.get(2)
    for article in user.articles:
        print(f"文章标题:{article.title}")
    return "文章查询成功！"

if __name__ == "__main__":
    app.run(debug=True)
